package edu.uwm.universitydb.dao.user;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import edu.uwm.universitydb.framework.nontransactional.db.command.impl.NonTransactionalDBFetchCommandBase;
import edu.uwm.universitydb.model.Role;
import edu.uwm.universitydb.model.User;

/**
 * Command class to fetch a user based on Id <code>SearchUserByIdCommand</type> here
 * 
 * @author Shyam Nair
 */
public class SearchUserByIDCommand extends NonTransactionalDBFetchCommandBase<User>
{

    /** string representing user name column in result set */
    private static final String TUPLE_COLUMN_USERNAME = "USERNAME";

    /** string representing role id column in result set */
    private static final String TUPLE_COLUMN_ROLEID = "ROLEID";

    /** string representing the SQL query */
    private static final String SQL_QUERY =
        "select university.users.username "
            + TUPLE_COLUMN_USERNAME
            + ", university.users.role_id "
            + TUPLE_COLUMN_ROLEID
            + " from university.users, university.roles "
            + "where university.users.username like ? AND university.users.role_id = university.roles.role_id";

    /** attribute holding user name */
    private final String mUserName;

    /**
     * 
     * @param userName
     */
    public SearchUserByIDCommand(final String userName)
    {
        mUserName = userName;
    }

    /*
     * (non-javadoc)
     * @see edu.uwm.cs757.framework.db.command.DataFetchCommand#processResult(java.sql.ResultSet)
     */
    @Override
    public User processResult(final ResultSet result) throws SQLException
    {
        // create a list
        while (result.next())
        {
            // process one tuple
            final Integer roleId = result.getInt(TUPLE_COLUMN_ROLEID);
            final Role role = Role.getRole(roleId);
            final String userName = result.getString(TUPLE_COLUMN_USERNAME);
            // add it to list
            return new User(userName, role);

        }
        // return the list
        //TODO throw exception
        return null;
    }

    /*
     * (non-javadoc)
     * @see edu.uwm.cs757.framework.db.command.DBCommand#getSQL()
     */
    @Override
    public String getSQL()
    {
        return SQL_QUERY;
    }

    /*
     * (non-javadoc)
     * @see edu.uwm.cs757.framework.db.command.DBCommand#setParameters(java.sql.PreparedStatement)
     */
    @Override
    public void setParameters(final PreparedStatement pStmt) throws SQLException
    {
        pStmt.setString(1, "%" + mUserName + "%");
    }

}